Importing and referencing dimensions

You can import dimensions or reference existing dimensions as needed.

Importing dimensions

You can import dimensions from other data marts in the same project. This is especially useful if:

• Several developers are working on the same data mart, developing different complex dimensions
• You need to use a dimension from another data mart and modify it slightly

To import dimensions

1. Open the Manage Data Marts window and click the Import or Reference Dimensions toolbar button.
2. From the Source data mart drop-down list, select the data mart containing the dimensions to import.
3. Select Import the selected dimensions.

4. Select which dimensions to import and then click OK.

   Information note

   Only dimensions that do not already exist in the current data mart (with same name) are available for selection.

   The dimensions are imported to your data mart.

Referencing dimensions

The ability to reference dimensions improves data mart design efficiency and execution flexibility by facilitating the reuse of data sets. Reuse of dimension tables across data marts allows you to break up fact tables into smaller units of work for both design and data loading, while ensuring consistency of data for analytics.

To add a referencing dimension:

1. Open the Manage Data Marts window and click the Import or Reference Dimensions toolbar button.
2. In the Import or Reference Dimensions window, select the Source data mart and then select Reference the selected dimension.

3. Select which dimensions you want to reference, then click OK.

   The dimensions are added to the data mart.

   Information noteReferencing Dimension names have the following format: <dimension name>_<data mart name>

   

   Information noteReferencing dimensions are read-only.

4. To add the newly added dimension to the star schema, right-click the dimension and then select Add to Star Schema.

   The Add Dimension <name> to Star Schema window opens.

5. Select which star schema(s) you want to add the dimension to and then click OK.

   Information noteAfter adding the referencing dimension to the star schema, you might see a icon next to the star schema name. This means that you need to validate and adjust the data mart containing the referenced dimension.

Working with referenced dimensions

It's important to be aware of the limitations and considerations when referencing other dimensions as well as the best practice guidelines.

Limitations and considerations

• Referenced dimensions cannot be deleted from the source data mart.
• Date and time dimensions cannot be referenced.
• Data lineage will not show all of the referenced dimensions.

• Deleting a dimension that references another referenced dimension should be done with caution. For example, If dimension X is referencing dimension Y which in turn is referencing dimension Z, deleting dimension Y will affect dimension X as well.

• Referenced dimensions must be created in the same database as the star schema or fact using them. They can be in a different schema however.

Best practices

• To prevent data inconsistencies, make sure that the source data marts ( i.e. the data marts containing the original dimensions) are processed before any data marts referencing those dimensions.

• To ensure correct processing of referenced dimensions, it is preferable to avoid circular references. An example of a circular reference is if Data Mart B references Dimension A in Data Mart A and Data Mart A references Dimension B in Data Mart B.

  Information noteIn some cases, it is okay to use circular references. If, for example, both Data Mart A and Data Mart B are incrementally updated, then any updates to Data Mart A will use the current version of Data Mart B, and vice versa.

• Conformed referenced dimensions that are used by one or more data marts should be grouped into a single data mart, without fact tables.
• Transactional fact tables should be grouped into data marts, based on processing requirements.
• Aggregate and State-oriented star schemas (fact tables) are typically processed during batch windows as they require complete rebuilds. It is therefore recommended practice to separate Aggregate and State-oriented fact tables from Transactional fact tables. Doing so, allows Transactional fact tables to be processed incrementally throughout the day as required, while allowing Aggregate and State-oriented fact tables to be processed during batch windows.